﻿"""
分光数据处理流程
1. 选出ERP工单已完工且在表Sorting_Info中的LotStatus为-1的工单，如果查询到的工单过多，可以控制一下数量，比如前10条
2. 读取工单对应的lotid、电流、电压和光通量；
3. 记录对应的lotID，利用四分位数检测异常值；
4. 将Sorting_Data的正常值的DataStatus由-1设置为1，异常值设置为0
5. 读取工单+lot+正常值的数据
6. 统计该工单+lot下数据总数与有效值数量
7. 统计相关指标，并写入数据库
8. 将工单+lot在表Sorting_Info中的LotStatus设置为1，表示已经处理完结。
"""
# 导入需要的库
import pyodbc
import pandas as pd
from sqlalchemy import create_engine
from Mac_rate import get_mac_rate
# 创建SQL server 数据库连接
engine = create_engine(
    "mssql+pyodbc://qaupd:qaupd8100@172.18.65.31:1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",fast_executemany=True)

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.18.65.31,1433;'
                      'DATABASE=SortingDB;PWD=qaupd8100;UID=qaupd')
# 创建游标
cursor = cnxn.cursor()
# 显示所有的列 方便调试
pd.set_option('display.max_columns', None)
# 正式应用应前置进行工单号筛选
def calculate(MoID):
    # 读取数据用于分析异常点
    df1 = pd.read_sql(
        f"Select LotID,Current_mA,ForwardVoltage_V,LuminousFlux_lm From Sorting_Data where moID='{MoID}' ORDER BY ID",
        engine)
    # 拼接LotID序列,只有一个批号需要去掉逗号，不然拼接sql语句多一个逗号会报错
    ids = tuple(df1['LotID'].drop_duplicates(keep='first', inplace=False).tolist())
    if len(ids) == 1:
        lotids = str(ids).replace(',', '')
    else:
        lotids = str(ids)
    # 简单画图看分布
    #df1['LuminousFlux_lm'].plot.hist(alpha=0.5)
    # 计算1/4 3/4位数和四分间距
    df_IQR = df1.loc[:, ['Current_mA', 'ForwardVoltage_V', 'LuminousFlux_lm']].quantile([0.25, 0.5, 0.75])
    # 计算箱线图上下限,上限Q3 + 1.5*IQR 下限Q1 - 1.5*IQR
    pras = ['Current_mA', 'ForwardVoltage_V', 'LuminousFlux_lm']
    for i in pras:
        df_IQR.loc['min', i] = df_IQR.loc[0.25, i] - 1.5 * (df_IQR.loc[0.75, i] - df_IQR.loc[0.25, i])
    for i in pras:
        df_IQR.loc['max', i] = df_IQR.loc[0.75, i] + 1.5 * (df_IQR.loc[0.75, i] - df_IQR.loc[0.25, i])
    # 将表Sorting_Data DataStatus由-1更新为1，表示全部数据有效
    cursor.execute(f"UPDATE Sorting_Data SET DataStatus=1 WHERE moID='{MoID}' AND LotID IN {lotids}")
    cnxn.commit()
    # 将表Sorting_Data不满足箱线图的异常点DataStatus更新为0
    cursor.execute(
        f"UPDATE Sorting_Data SET DataStatus=0 WHERE moID='{MoID}' AND LotID IN {lotids} AND ({df_IQR['Current_mA']['min']} > Current_mA OR Current_mA > {df_IQR['Current_mA']['max']} OR {df_IQR['ForwardVoltage_V']['min']}>ForwardVoltage_V OR ForwardVoltage_V>{df_IQR['ForwardVoltage_V']['max']} OR {df_IQR['LuminousFlux_lm']['min']}>LuminousFlux_lm OR LuminousFlux_lm>{df_IQR['LuminousFlux_lm']['max']})")
    cnxn.commit()
    # 读取没有异常的数据
    df2 = pd.read_sql(
        f"Select Current_mA,ForwardVoltage_V,Power_W,LuminousFlux_lm,RadiantFlux_mW,LumiousEfficacy_lmPerW,CIEx,CIEy,Ra,R9 From Sorting_Data where moID='{MoID}'AND LotID IN {lotids}AND DataStatus=1 ORDER BY ID",
        engine)
    # 简单画图看分布
    #df2['LuminousFlux_lm'].plot.hist(alpha=0.5)
    # 创建dataframe格式的结果
    result = pd.DataFrame({'MoID':[MoID],'LotIDs':[str(ids)],'Total_Counts':[df1.shape[0]],'Valid_Counts':[df2.shape[0]]})
    df2['LuminousFlux_lm'].describe()
    # 创建统计参数列表
    pras = ['mean', 'std', 'min', 'max']
    # 对测量结果按参数进行统计并写入result
    for i in df2.columns:
        for j in pras:
            result.loc[0, i + '_' + j] = df2[i].describe()[j]
    #读取224数据库中的读取对应订单的LM，Ra等参数的上下限
    try:
        df3 = pd.read_sql(
            f"select DISTINCT MoID,[光通量上限],[光通量下限],QA_Finished_Part_Spec.[CIEx],QA_Finished_Part_Spec.[CIEy],[显指],QA_Finished_Part_Spec.[R9]FROM Sorting_Data,QA_Finished_Part_Spec,MO_ITEM WHERE Sorting_Data.MoID=MO_ITEM.DOC_NO COLLATE Chinese_PRC_90_CI_AI AND MO_ITEM.ITEM_CODE=QA_Finished_Part_Spec.ITEM_CODE COLLATE Chinese_PRC_90_CI_AI AND moID='{MoID}'",
            engine)
        df3=df3.rename(columns={'光通量上限':"lm_usl",'光通量下限':'lm_lsl','CIEx':'CIEx_centre','CIEy':'CIEy_centre','显指':'Ra_lsl','R9':'R9_lsl'})
    except:
        pass
    #这里注意由于从数据导出的数据是str，需要转换格式,有部分lm上限没有
    try:
        ppk_lm=(min(float(df3.loc[0,'lm_usl'])-df2['LuminousFlux_lm'].mean(),df2['LuminousFlux_lm'].mean()-float(df3.loc[0,'lm_lsl'])))/(3*df2['LuminousFlux_lm'].std())
    except:
        ppk_lm=404
    # 规格中Ra有字母，R9有/或其他字母需要处理
    try:
        ppk_Ra = (df2['Ra'].mean() - float(df3.loc[0, 'Ra_lsl'])) / (3 * df2['Ra'].std())
    except:
        ppk_Ra=404  #这里用404此数据无效代表错误
    try:
        ppk_R9 = (df2['R9'].mean() - float(df3.loc[0, 'R9_lsl'])) / (3 * df2['R9'].std())
    except:
        ppk_R9=404
    #计算订单色坐标平均值与中心点差异
    try:
        x1=df2['CIEx'].mean()
        x2=float(df3.loc[0,'CIEx_centre'])
        y1=df2['CIEy'].mean()
        y2=float(df3.loc[0,'CIEy_centre'])
        d_x=x1-x2
        d_y=y1-y2
        d_xy=pow(pow(x1-x2,2)+pow(y1-y2,2),0.5)

    #写入result
        result.loc[0, 'lm_ppk'] = ppk_lm
        result.loc[0, 'Ra_ppk'] = ppk_Ra
        result.loc[0, 'R9_ppk'] = ppk_R9
        result.loc[0, 'x_d'] =d_x
        result.loc[0, 'y_d'] =d_y
        result.loc[0, 'xy_d'] =d_xy
    except:
        pass
    # 将表Sorting_Info LotStatus更新为1 代表该工单与批次的数据已经处理
    cursor.execute(f"UPDATE Sorting_Info SET LotStatus=1 WHERE moID='{MoID}' AND LotID IN {lotids}")
    cnxn.commit()#如果把这个放到最后速度是不是会快一些？
    #把算出的mac rate 加入到sorting analysis中
    df10 = get_mac_rate(MoID)
    df11=pd.concat([result,df10],axis=1)
    df11.to_excel("123.xlsx")
    # 写入数据
    df11.to_sql(name='Sorting_Data_Analysis', con=engine, if_exists='append', index=False)


if __name__ == '__main__':
    df = pd.read_sql(f"Select DISTINCT TOP 2000 MoID From Sorting_Data where DataStatus=-1 ORDER BY MoID ", engine) #定义选取工单数
    list1 = list(df.MoID)  # 把series 工单号变成一个列表形式
    print(list1)
    n=0
    for MoID in list1: #把定义出的工单进行数据分析
        calculate(MoID)
        n=n+1
        print(str(n)+':'+MoID)
    print("全部写入完成")










